package cn.com.bluemoon.daps.model.core.upload;

import cn.com.bluemoon.daps.common.domain.ResultBean;
import cn.com.bluemoon.daps.common.exception.DapException;
import cn.com.bluemoon.daps.model.entity.DapDataModelTable;
import cn.com.bluemoon.daps.model.entity.DapDataModelTableField;
import cn.com.bluemoon.daps.model.service.DapDataModelTableFieldService;
import cn.com.bluemoon.daps.model.service.DapDataModelTableService;
import cn.com.bluemoon.daps.standard.api.RemoteSystemCategoryService;
import cn.com.bluemoon.daps.standard.entity.DapSystemCategory;
import cn.com.bluemoon.daps.standard.vo.SysTopicCategorySimpleVo;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.util.WorkBookUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.annotation.concurrent.NotThreadSafe;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Jarod.Kong
 */
@Component
@Slf4j
@NotThreadSafe
public class DapModelExeclExport {

    @Autowired
    private DapDataModelTableService tableService;

    @Autowired
    private DapDataModelTableFieldService fieldService;

    @Autowired
    private RemoteSystemCategoryService remoteSystemCategoryService;

    private CellStyle tableHeadStyle, fieldHeadStyle;

    public String exportExcel(String modelId, String groupId) throws IOException {
        LambdaQueryWrapper<DapDataModelTable> findTableWrapper = tableService.bmLambdaQuery().eq(DapDataModelTable::getDataModelId, modelId)
                .eq(DapDataModelTable::getDataNewest, 1)
                .eq(!StringUtils.isEmpty(groupId), DapDataModelTable::getGroupId, groupId);
        List<DapDataModelTable> tables = tableService.list(findTableWrapper);
        if (tables.isEmpty()){
            throw new DapException("获取模型表为空，不进行导出！");
        }
        List<String> tableIds = tables.stream().map(DapDataModelTable::getId).distinct().collect(Collectors.toList());
        List<String> categoryIds = tables.stream().map(DapDataModelTable::getCategoryId)
                .flatMap(s->Arrays.stream(s.split(","))).distinct().collect(Collectors.toList());
        Map<String, String> categoryIdAndName = topicCategoryMap(categoryIds);

        LambdaQueryWrapper<DapDataModelTableField> findTableFieldsWrapper = fieldService.bmLambdaQuery()
                .eq(DapDataModelTableField::getDataModelId, modelId).eq(DapDataModelTableField::getDataNewest, 1)
                .in(DapDataModelTableField::getModelTableId, tableIds)
                .orderByDesc(DapDataModelTableField::getModelTableId).orderByAsc(DapDataModelTableField::getFieldRank);
        Map<String, List<DapDataModelTableField>> tableIdAndFields = fieldService.list(findTableFieldsWrapper).stream()
                .collect(Collectors.groupingBy(DapDataModelTableField::getModelTableId));
        List<SysTopicCategorySimpleVo> topicCategory = getTopicCategory();

        String filePath = Files.createTempFile("temp_model_export_v2", ".xlsx").toFile().getAbsolutePath();
        try (OutputStream outputStream = new FileOutputStream(filePath); Workbook workbook = new XSSFWorkbook()) {
            tableHeadStyle = getHeadCellStyle(workbook, IndexedColors.SKY_BLUE);
            fieldHeadStyle = getHeadCellStyle(workbook, IndexedColors.LIGHT_YELLOW);
            createModelSheet(workbook, tables, tableIdAndFields, categoryIdAndName);
            createTopicCategorySheet(workbook, topicCategory);
            workbook.write(outputStream);
        }
        return filePath;
    }

    private Map<String,String> topicCategoryMap(List<String> ids) {
        ResultBean<List<DapSystemCategory>> resultBean = null;
        try {
            resultBean = remoteSystemCategoryService.getSystemCategories(ids);
            if (resultBean.isOk()){
                return resultBean.getContent().stream().collect(Collectors.toMap(s->s.getId(), s->s.getCodeName(), (a,b)->a));
            }
        } catch (Exception e) {
            log.error("远程获取主题分类失败", e);
            throw new DapException("远程获取主题分类失败", e);
        }
        return Collections.emptyMap();
    }

    private List<SysTopicCategorySimpleVo> getTopicCategory() {
        ResultBean<List<SysTopicCategorySimpleVo>> resultBean = null;
        try {
            resultBean = remoteSystemCategoryService.sysTopicCategories();
        } catch (Exception e) {
            log.error("远程获取主题分类失败", e);
            throw new DapException("远程获取主题分类失败", e);
        }
        List<SysTopicCategorySimpleVo> systemCategoryTreeVos = resultBean.getContent();
        return systemCategoryTreeVos;
    }

    private void createTopicCategorySheet(Workbook workbook, List<SysTopicCategorySimpleVo> topicCategory) {
        Sheet sheet = workbook.createSheet("主题分类");
        sheet.setColumnWidth(0, 15 * 256);
        sheet.setColumnWidth(1, 15 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 15 * 256);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        int rowSize = -1;
        Row titleR = WorkBookUtil.createRow(sheet, ++rowSize);
        Cell cell = WorkBookUtil.createCell(titleR, 0, "数据截止时间："+ DateUtils.format(new Date()) +"（最新的请在平台查询）");
        cell.setCellStyle(getHeadCellStyle(workbook, IndexedColors.LIGHT_YELLOW));

        Row title2R = WorkBookUtil.createRow(sheet, ++rowSize);
        cell = WorkBookUtil.createCell(title2R, 0, "主题");
        cell.setCellStyle(getHeadCellStyle(workbook, IndexedColors.SEA_GREEN));
        cell = WorkBookUtil.createCell(title2R, 1, "一级分类");
        cell.setCellStyle(getHeadCellStyle(workbook, IndexedColors.SEA_GREEN));
        cell = WorkBookUtil.createCell(title2R, 2, "二级分类");
        cell.setCellStyle(getHeadCellStyle(workbook, IndexedColors.SEA_GREEN));
        cell = WorkBookUtil.createCell(title2R, 3, "三级分类");
        cell.setCellStyle(getHeadCellStyle(workbook, IndexedColors.SEA_GREEN));

        for (SysTopicCategorySimpleVo each : topicCategory) {
            // 创建一个表信息
            Row startTagR = WorkBookUtil.createRow(sheet, ++rowSize);
            WorkBookUtil.createCell(startTagR, 0, each.getTopic());
            WorkBookUtil.createCell(startTagR, 1, each.getFirstC());
            WorkBookUtil.createCell(startTagR, 2, each.getSecondC());
            WorkBookUtil.createCell(startTagR, 3, each.getThirdC());
        }
    }

    private void createModelSheet(Workbook workbook, List<DapDataModelTable> tables,
                                  Map<String, List<DapDataModelTableField>> tableIdAndFields,
                                  Map<String, String> categoryIdAndName) {
        Sheet sheet = workbook.createSheet("模型");
        sheet.setColumnWidth(0, 25 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 15 * 256);
        sheet.setColumnWidth(3, 10 * 256);
        sheet.setColumnWidth(4, 10 * 256);
        sheet.setColumnWidth(5, 10 * 256);
        sheet.setColumnWidth(6, 35 * 256);


        int rowSize = -1;

        for (DapDataModelTable table : tables) {
            // 创建一个表信息
            Row startTagR = WorkBookUtil.createRow(sheet, ++rowSize);
            Cell cell = WorkBookUtil.createCell(startTagR, 0, "<表>");
            cell.setCellComment(getComment(sheet, cell, "模型表开始标记符"));

            // 物理模型	逻辑模型	表业务描述	主题分类
            Row tableTitleR = WorkBookUtil.createRow(sheet, ++rowSize);
            cell = WorkBookUtil.createCell(tableTitleR, 0, "物理模型");
            cell.setCellStyle(tableHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.最大长度64个字符\n" +
                    "2.不允许为空、不允许重复\n" +
                    "3.组成：大小写字母、、数字、下划线\n"));
            cell = WorkBookUtil.createCell(tableTitleR, 1, "逻辑模型");
            cell.setCellStyle(tableHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.最大长度2048个字符\n" +
                    "2.不允许为空\n" +
                    "3.对应模型表中文名\n"));
            cell = WorkBookUtil.createCell(tableTitleR, 2, "表业务描述");
            cell.setCellStyle(tableHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.最大长度500个字符"));
            cell = WorkBookUtil.createCell(tableTitleR, 3, "主题分类");
            cell.setCellStyle(tableHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.可参考\"主题分类\"sheet的内容\n" +
                    "2.格式：主题/分类，且主题和一级分类是必填的\n"));

            Row tableInfoR = WorkBookUtil.createRow(sheet, ++rowSize);
            WorkBookUtil.createCell(tableInfoR, 0, table.getEngName());
            WorkBookUtil.createCell(tableInfoR, 1, table.getName());
            WorkBookUtil.createCell(tableInfoR, 2, table.getBusinessDesc());
            String topicCategory = Arrays.stream(table.getCategoryId().split(",")).map(s -> categoryIdAndName.getOrDefault(s, s)).collect(Collectors.joining("/"));
            WorkBookUtil.createCell(tableInfoR, 3, topicCategory);

            // 字段名称	字段英文名	字段类型	是否主键	是否为空	缺省	备注
            Row fieldTitleR = WorkBookUtil.createRow(sheet, ++rowSize);
            cell = WorkBookUtil.createCell(fieldTitleR, 0, "字段名称");
            cell.setCellStyle(fieldHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.长度1024个字符"));
            cell = WorkBookUtil.createCell(fieldTitleR, 1, "字段英文名");
            cell.setCellStyle(fieldHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.最大长度64个字符\n" +
                    "2.不允许为空\n" +
                    "3.组成：大小写字母、、数字、下划线\n"));
            cell = WorkBookUtil.createCell(fieldTitleR, 2, "字段类型");
            cell.setCellStyle(fieldHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.最大长度500个字符"));
            cell = WorkBookUtil.createCell(fieldTitleR, 3, "是否主键");
            cell.setCellStyle(fieldHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "模型导入：只有yes是主键，其他为否"));
            cell = WorkBookUtil.createCell(fieldTitleR, 4, "是否为空");
            cell.setCellStyle(fieldHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "模型导入：只有no是非空，其他为空"));
            cell = WorkBookUtil.createCell(fieldTitleR, 5, "缺省");
            cell.setCellStyle(fieldHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.长度1024\n" +
                    "2.需要符合数据类型的约束\n"));
            cell = WorkBookUtil.createCell(fieldTitleR, 6, "备注");
            cell.setCellStyle(fieldHeadStyle);
            cell.setCellComment(getComment(sheet, cell, "1.最大长度为500个字符"));
            // list
            for (DapDataModelTableField field : tableIdAndFields.getOrDefault(table.getId(), Lists.newArrayList())) {
                Row fieldInfoR = WorkBookUtil.createRow(sheet, ++rowSize);
                WorkBookUtil.createCell(fieldInfoR, 0, field.getComment());
                WorkBookUtil.createCell(fieldInfoR, 1, field.getFieldName());
                WorkBookUtil.createCell(fieldInfoR, 2, getFullFieldType(field));
                WorkBookUtil.createCell(fieldInfoR, 3, field.getPrimaryKey() == 1 ? "yes" : "");
                WorkBookUtil.createCell(fieldInfoR, 4, field.getNotNull() == 1 ? "no" : "");
                WorkBookUtil.createCell(fieldInfoR, 5, field.getDefaultValue());
                WorkBookUtil.createCell(fieldInfoR, 6, field.getBusinessDesc());
            }

            Row endTagR = WorkBookUtil.createRow(sheet, ++rowSize);
            cell = WorkBookUtil.createCell(endTagR, 0, "</表>");
            cell.setCellComment(getComment(sheet, cell, "模型表结束标记符"));

            // emptyRow
            WorkBookUtil.createRow(sheet, ++rowSize);

        }
    }

    private String getFullFieldType(DapDataModelTableField field) {
        String fieldType;
        List<String> seg = new ArrayList<>();
        if (!StringUtils.isEmpty(field.getLength())){
            seg.add(field.getLength());
        }
        if (!StringUtils.isEmpty(field.getDecimalLength())){
            seg.add(String.valueOf(field.getDecimalLength()));
        }
        if (!seg.isEmpty()){
            fieldType = field.getDataType().trim() + String.format("(%s)", String.join(",", seg));
        }else {
            fieldType = field.getDataType();
        }
        return fieldType;
    }

    private CellStyle getHeadCellStyle(Workbook workbook, IndexedColors indexedColors) {
        CellStyle fieldHeadStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        font.setBold(true);
        fieldHeadStyle.setFont(font);
        fieldHeadStyle.setWrapText(true);
        fieldHeadStyle.setBorderBottom(BorderStyle.THIN);
        fieldHeadStyle.setBorderLeft(BorderStyle.THIN);
        fieldHeadStyle.setBorderRight(BorderStyle.THIN);
        fieldHeadStyle.setBorderTop(BorderStyle.THIN);
        fieldHeadStyle.setFillForegroundColor(indexedColors.getIndex());
        fieldHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return fieldHeadStyle;
    }

    private Comment getComment(Sheet sheet, Cell cell, String commentText) {
        Drawing<?> draw = sheet.createDrawingPatriarch();
        Comment comment = draw.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,
                cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex() + 1, cell.getRowIndex() + 1));
        comment.setString(new XSSFRichTextString(commentText));
        return comment;
    }

}
